-- @owner:songfei_st
-- @date:2024-8-2
-- @testpoint:1.嵌套数组类型，触发器中使用，部分用例合理报错          


--prestep1:切换A库;expect:执行成功
@conn PrimaryDbAmode;

--创建一个表，用来存需验证的数据
create table t_compare_Case0022(first_name varchar(100), last_name varchar(100));

--step1:创建表;expect:成功
create table t_PLArray_Case0022(first_name varchar(100), last_name varchar(100));

--step2:创建触发器函数;expect:成功
create or replace function f_PLArray_Case0022() returns trigger as
$$
declare
   type typ_PLArray_1 is varray(3) of varchar(100);
   type typ_PLArray_2 is varray(4) of typ_PLArray_1;
   nstarr typ_PLArray_2;
begin
   nstarr(2)(1):=new.first_name;
   nstarr(2)(2):=new.last_name;
   insert into t_compare_Case0022(first_name,last_name) values (nstarr(2)(1),nstarr(2)(2));
   return new;
end
$$ language plpgsql;
/

--step3:创建INSERT触发器;expect:成功
create trigger tri_PLArray_Case0022 before insert on t_PLArray_Case0022 for each row
execute procedure f_PLArray_Case0022();/

--step4:调用触发器;expect:成功,Jason      | Statham
insert into t_PLArray_Case0022 values('Jason','Statham');

--step5:查询数据;expect:成功,
select * from t_compare_Case0022;

--清理环境;expect:成功(4 rows)
drop trigger tri_PLArray_Case0022 on t_PLArray_Case0022;
drop function f_PLArray_Case0022;
drop table t_PLArray_Case0022;
drop table t_compare_Case0022;
